/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.company.user.dao.impl;

import com.company.regis.bean.UserCriteria;
import com.company.regis.db.BaseDao;
import com.company.regis.db.entity.RegUser;
import com.company.regis.db.entity.ViewUserDetail;
import com.company.user.dao.UserDao;
import java.util.ArrayList;
import java.util.List;
import org.springframework.stereotype.Repository;

/**
 *
 * @author totoland
 */
@Repository("userDao")
public class UserDaoImpl extends BaseDao implements UserDao {

    String SQL = "SELECT REG_USER.USER_ID, "
            + "  REG_USER.USER_NAME, "
            + "  REG_USER.USER_PASSWORD, "
            + "  REG_USER.FIRST_NAME, "
            + "  REG_USER.LAST_NAME, "
            + "  REG_USER.GENDER, "
            + "  REG_USER.USER_GROUP, "
            + "  REG_ADDRESS.PROVINCE_ID, "
            + "  REG_ADDRESS.AMPHUR_ID, "
            + "  AMPHUR.AMPHUR_NAME, "
            + "  REG_ADDRESS.TUMBOL_ID, "
            + "  PROVINCE.PROVINCE_NAME, "
            + "  DISTRICT.DISTRICT_NAME "
            + "FROM REG_USER "
            + "INNER JOIN REG_ADDRESS "
            + "ON REG_USER.USER_ID = REG_ADDRESS.USER_ID "
            + "LEFT JOIN PROVINCE "
            + "ON REG_ADDRESS.PROVINCE_ID = PROVINCE.PROVINCE_ID "
            + "LEFT JOIN AMPHUR "
            + "ON REG_ADDRESS.AMPHUR_ID = AMPHUR.AMPHUR_ID "
            + "LEFT JOIN DISTRICT "
            + "ON REG_ADDRESS.TUMBOL_ID = DISTRICT.DISTRICT_ID";

    @Override
    public List<ViewUserDetail> getAllUserDetail() {
        return findNativeQuery(SQL, ViewUserDetail.class);
    }

    @Override
    public RegUser findById(Integer id) {
        return (RegUser) findUniqNativeQuery("SELECT * FROM REG_USER WHERE USER_ID = ? ", RegUser.class, id);
    }

    @Override
    public List<ViewUserDetail> findByCriteria(UserCriteria criteria) {
        List<Object> lst = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder();
        sql.append(SQL).append(" WHERE 1 = 1 ");

        if (criteria.getUserName() != null && !criteria.getUserName().isEmpty()) {
            sql.append("AND REG_USER.USER_NAME = ? ");
            lst.add(criteria.getUserName());
        }

        if (criteria.getFirstName() != null && !criteria.getFirstName().isEmpty()) {
            sql.append("AND REG_USER.FIRST_NAME = ? ");
            lst.add(criteria.getFirstName());
        }

        if (criteria.getProvinceId() != null && !criteria.getProvinceId().isEmpty()) {
            sql.append("AND REG_ADDRESS.PROVINCE_ID = ? ");
            lst.add(criteria.getProvinceId());
        }

        return findNativePagginQueryOracle(sql.toString(), criteria.getStartRow(), criteria.getMaxRow(), ViewUserDetail.class, lst.toArray());
    }

    @Override
    public Integer countByCriteria(UserCriteria criteria) {
        List<Object> lst = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) "
            + "FROM REG_USER "
            + "INNER JOIN REG_ADDRESS "
            + "ON REG_USER.USER_ID = REG_ADDRESS.USER_ID "
            + "LEFT JOIN PROVINCE "
            + "ON REG_ADDRESS.PROVINCE_ID = PROVINCE.PROVINCE_ID "
            + "LEFT JOIN AMPHUR "
            + "ON REG_ADDRESS.AMPHUR_ID = AMPHUR.AMPHUR_ID "
            + "LEFT JOIN DISTRICT "
            + "ON REG_ADDRESS.TUMBOL_ID = DISTRICT.DISTRICT_ID").append(" WHERE 1 = 1 ");

        if (criteria.getUserName() != null && !criteria.getUserName().isEmpty()) {
            sql.append("AND REG_USER.USER_NAME = ? ");
            lst.add(criteria.getUserName());
        }

        if (criteria.getFirstName() != null && !criteria.getFirstName().isEmpty()) {
            sql.append("AND REG_USER.FIRST_NAME = ? ");
            lst.add(criteria.getFirstName());
        }

        if (criteria.getProvinceId() != null && !criteria.getProvinceId().isEmpty()) {
            sql.append("AND REG_ADDRESS.PROVINCE_ID = ? ");
            lst.add(criteria.getProvinceId());
        }

        return super.countNativeQuery(sql.toString(),lst.toArray());
    }
}
